﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLite.NET;

namespace BierdopjeTV.Database
{

	class GUISeries
		{
		
			#region Fields

			public int ShowID; 
			public string ShowName;
            public int Wachted;
            public int Downloaded;            
            public int SubsEN;
            public int SubsNL;
            public string Summary;
            public string LastAired;
            public string NextEpisode;
            public int Runtime;
            public string ShowStatus;
            public string Score;


			
			#endregion
			
			
	
			

		}
		
		
    static class DBShows
    {

		public static List<GUISeries> getShows()
        {
            System.Diagnostics.Debug.Print("DBShows.getShows()");
			List<GUISeries> _BierdopjeShows = new List<GUISeries>();
            		
			GUISeries _Show;
            string _Query;
            _Query = "";
            _Query += "SELECT" + Environment.NewLine;
            _Query += "	(SELECT Sum(SubENDownloaded) FROM BierdopjeEpisodes WHERE ShowID = BierdopjeShows.ShowID AND Downloaded = 1) AS SubEN," + Environment.NewLine;
            _Query += "	(SELECT Sum(SubNLDownloaded) FROM BierdopjeEpisodes WHERE ShowID = BierdopjeShows.ShowID AND Downloaded = 1) AS SubNL," + Environment.NewLine;
            _Query += "	Sum (Watched) As Gezien," + Environment.NewLine;
            _Query += "	Sum(Downloaded) AS Gedownload," + Environment.NewLine;
            _Query += "	BierdopjeShows.*" + Environment.NewLine;
            _Query += "" + Environment.NewLine;
            _Query += "" + Environment.NewLine;
            _Query += "FROM" + Environment.NewLine;
            _Query += "	BierdopjeShows" + Environment.NewLine;
            _Query += "LEFT JOIN" + Environment.NewLine;
            _Query += "	BierdopjeEpisodes ON BierdopjeShows.ShowID = BierdopjeEpisodes.ShowID" + Environment.NewLine;
            if (Database.getSettingValue("AlleenLokaleEpisodes") == "True")
            {
                _Query += "WHERE" + Environment.NewLine;
                _Query += "	Downloaded = 1" + Environment.NewLine;
            } 
            _Query += "" + Environment.NewLine;
            _Query += "GROUP BY" + Environment.NewLine;
            _Query += "	ShowID" + Environment.NewLine;
            _Query += "ORDER BY" + Environment.NewLine;
            _Query += "	ShowName ASC" + Environment.NewLine;
            //System.Diagnostics.Debug.Print(_Query);

            SQLiteResultSet _Result = Helpers.SqliteHelper.ExecuteQuery(_Query);
			
			for (int i=0;i<_Result.Rows.Count;i++)
			{
				_Show = new GUISeries();
				_Show.ShowName =_Result.Rows[i].fields[(int)_Result.ColumnIndices["ShowName"]];
				_Show.ShowID = Convert.ToInt32(_Result.Rows[i].fields[(int)_Result.ColumnIndices["ShowID"]]);
                _Show.Downloaded = Convert.ToInt32(_Result.Rows[i].fields[(int)_Result.ColumnIndices["Gedownload"]]);
                _Show.Wachted = Convert.ToInt32(_Result.Rows[i].fields[(int)_Result.ColumnIndices["Gezien"]]);
                try
                {
                    _Show.SubsEN = Convert.ToInt32(_Result.Rows[i].fields[(int)_Result.ColumnIndices["SubEN"]]);
                    _Show.SubsNL = Convert.ToInt32(_Result.Rows[i].fields[(int)_Result.ColumnIndices["SubNL"]]);
                }
                catch { }
                _Show.Summary = _Result.Rows[i].fields[(int)_Result.ColumnIndices["Summary"]];
                _Show.NextEpisode = _Result.Rows[i].fields[(int)_Result.ColumnIndices["NextEpisode"]];
                _Show.LastAired = _Result.Rows[i].fields[(int)_Result.ColumnIndices["LastAired"]];
                _Show.ShowStatus = _Result.Rows[i].fields[(int)_Result.ColumnIndices["ShowStatus"]];
                _Show.Score = _Result.Rows[i].fields[(int)_Result.ColumnIndices["Score"]];                
                _Show.Runtime = Convert.ToInt32(_Result.Rows[i].fields[(int)_Result.ColumnIndices["RunTime"]]);

                //We have seen 12 of 10 downloaded episodeS? ;-)
                if (_Show.Wachted > _Show.Downloaded)
                {
                    _Show.Wachted = _Show.Downloaded;
                }
				_BierdopjeShows.Add(_Show);
				
                _Show = null;
				
			}

            return _BierdopjeShows;

        }

        
    }
}
